package com.bt.mes.brm.provider.service;

import static com.bt.mes.common.bean.def.GenericDef.ACTION_FLG_ADD;
import static com.bt.mes.common.bean.def.GenericDef.ACTION_FLG_CANCEL;
import static com.bt.mes.common.bean.def.GenericDef.ACTION_FLG_DELETE;
import static com.bt.mes.common.bean.def.GenericDef.ACTION_FLG_INQUIRE;
import static com.bt.mes.common.bean.def.GenericDef.ACTION_FLG_NEW;
import static com.bt.mes.common.bean.def.GenericDef.ACTION_FLG_QUERY;
import static com.bt.mes.common.bean.def.GenericDef.ACTION_FLG_REPLACE;
import static com.bt.mes.common.bean.def.GenericDef.ACTION_FLG_UPDATE;
import static com.bt.mes.common.bean.def.GenericDef.E_ADD_EXIST;
import static com.bt.mes.common.bean.def.GenericDef.E_BIS_DATA;
import static com.bt.mes.common.bean.def.GenericDef.E_READ_NOT_FOUND;
import static com.bt.mes.common.bean.def.GenericDef.E_XPLSTDAT_OTHER_ERROR;
import static com.bt.mes.common.bean.def.GenericDef.RETURN_CODE_OK;
import static com.bt.mes.common.bean.def.GenericDef.RETURN_CODE_SET_ERROR;
import static com.bt.mes.common.bean.def.GenericDef.RETURN_CODE_UNKNOWN;
import static com.bt.mes.common.bean.def.GenericDef.RETURN_MESG_OK;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import com.bt.mes.brm.api.service.IXplstdatService;
import com.bt.mes.brm.provider.tx.Xpbmdldf.XpbmdldfI;
import com.bt.mes.brm.provider.tx.Xplstdat.XplstdatI;
import com.bt.mes.brm.provider.tx.Xplstdat.XplstdatIA;
import com.bt.mes.brm.provider.tx.Xplstdat.XplstdatO;
import com.bt.mes.brm.provider.tx.Xplstdat.XplstdatOA;
import com.bt.mes.common.dao.IBisDataDao;
import com.bt.mes.common.util.GUIDGenerator;
import com.bt.mes.common.util.JacksonUtil;
import com.bt.mes.common.util.Util;
import com.bt.table.ppt.Bis_data;

@Service("xplstDatService")
public class XplstdatService implements IXplstdatService {

	private Logger logger = Logger.getLogger(XplstdatService.class);
	private XplstdatI inTrx;
	private XplstdatO outTrx;

	@Autowired
	private IBisDataDao bisDataDao;

	public String subMainProc(String strInMsg) {
		logger.info("InTrx is " + strInMsg);
		Session session = bisDataDao.getCrSession();
		String strOutTrx = null;
		try {
			inTrx = new XplstdatI();
			outTrx = new XplstdatO();
			inTrx = JacksonUtil.fromJson(strInMsg, XplstdatI.class);
			outTrx.setTrx_id("XPLSTDAT");
			outTrx.setType_id("O");
			char action_flg = inTrx.getAction_flg().charAt(0);
			switch (action_flg) {
			case ACTION_FLG_QUERY:
				outTrx = queryByCondition();
				break;
			case ACTION_FLG_ADD:
				outTrx = addFnc();
				break;
			case ACTION_FLG_UPDATE:
				outTrx = updateFnc();
				break;
			case ACTION_FLG_DELETE:
				outTrx = deleteFnc();
				break;
			case ACTION_FLG_NEW:
				outTrx = addDataCateProc();
				break;
			case ACTION_FLG_REPLACE:
				outTrx = updateDataCateProc();
				break;
			case ACTION_FLG_CANCEL:
				outTrx = deleteDataCateProc();
				break;
			case ACTION_FLG_INQUIRE:
				outTrx = queryByExt();
				break;
			case 'T':
				outTrx = deleteByDataCateExt1();
				break;
			case 'W':
				outTrx = addByDataCateExt1();
				break;
			case 'S':
				outTrx = queryDataByCateUser();
				break;
			case 'O':
				outTrx = listDataBySort();
				break;
			case 'B':
				outTrx = queryByExt5();
				break;
			case 'F':
				outTrx = queryDataByFabUser();
				break;
			case 'E':
				outTrx = queryByConditioncs();
				break;
			case 'G':
				outTrx = queryformaker();
				break;
			case 'H':
				outTrx = querybycate();
				break;
			default:
				outTrx = errActionFnc();
			}
			if (RETURN_CODE_OK.equals(outTrx.getRtn_code())) {
				session.flush();
			} else {
				TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
			}
		} catch (Exception ex) {
			outTrx.setRtn_code(RETURN_CODE_UNKNOWN);
			outTrx.setRtn_mesg(ex.getCause().toString());
			TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
		} finally {
			strOutTrx = JacksonUtil.toJSONStr(outTrx);
		}
		return strOutTrx;

	}

	public XplstdatO addFnc() {

		XplstdatO xplstdatO = new XplstdatO();
		xplstdatO.setTrx_id("XPLSTDAT");
		xplstdatO.setType_id("O");
		List<XplstdatOA> oaryList = new ArrayList<XplstdatOA>();

		try {
			List<XplstdatIA> iaryList = inTrx.getIary();
			// long tbl_cnt = xplstdatI.getTbl_cnt();
			for (XplstdatIA iary : iaryList) {
				Bis_data bis_data = new Bis_data();

				Bis_data tmp_bis_data = getDataByKeyProc(iary.getData_cate(), iary.getData_id(), iary.getData_ext());
				if (tmp_bis_data != null && !iary.getData_cate().equals("DFCT")) {
					xplstdatO.setRtn_code(E_BIS_DATA + E_ADD_EXIST + "");
					xplstdatO.setRtn_mesg("Record Already Exist");
					return xplstdatO;
				}
				if (iary.getData_cate().equals("CUSD")) {
					String hql = "FROM Bis_data where data_item ='" + iary.getData_item() + "'";
					List<Bis_data> bis_dataList = bisDataDao.find(hql);
					if (bis_dataList.size() > 0) {
						xplstdatO.setRtn_code(E_BIS_DATA + E_ADD_EXIST + "");
						xplstdatO.setRtn_mesg("Record Already Exist");
						return xplstdatO;
					}
				}
				bis_data.setData_seq_id(GUIDGenerator.javaGUID());
				bis_data.setData_cate(iary.getData_cate());
				bis_data.setData_id(iary.getData_id());
				bis_data.setData_ext(iary.getData_ext());
				bis_data.setData_item(iary.getData_item());
				bis_data.setExt_1(iary.getExt_1());
				bis_data.setExt_2(iary.getExt_2());
				bis_data.setExt_3(iary.getExt_3());
				bis_data.setExt_4(iary.getExt_4());
				bis_data.setExt_5(iary.getExt_5());
				bis_data.setData_desc(iary.getData_desc());
				bisDataDao.save(bis_data);

				XplstdatOA oary = new XplstdatOA();
				oary.setData_seq_id(bis_data.getData_seq_id());
				oary.setData_cate(bis_data.getData_cate());
				oary.setData_id(bis_data.getData_id());
				oary.setData_ext(bis_data.getData_ext());
				oary.setData_item(bis_data.getData_item());
				oary.setData_desc(bis_data.getData_desc());
				oary.setExt_1(bis_data.getExt_1());
				oary.setExt_2(bis_data.getExt_2());
				oary.setExt_3(bis_data.getExt_3());
				oary.setExt_4(bis_data.getExt_4());
				oary.setExt_5(bis_data.getExt_5());
				oaryList.add(oary);
			}
		} catch (Exception ex) {
			logger.error(Util.stackTraceToString(ex));
			xplstdatO.setRtn_code("0000003");
			xplstdatO.setRtn_mesg(Util.stackTraceToString(ex));
			return xplstdatO;
		}
		xplstdatO.setOary(oaryList);
		xplstdatO.setTbl_cnt(oaryList.size());
		xplstdatO.setRtn_code(RETURN_CODE_OK);
		xplstdatO.setRtn_mesg("success");
		return xplstdatO;
	}

	public XplstdatO updateFnc() {
		XplstdatO xplstdatO = new XplstdatO();
		try {
			List<XplstdatIA> iaryList = inTrx.getIary();
			for (XplstdatIA iary : iaryList) {
				Bis_data bis_data = null;
				String data_seq_id = iary.getData_seq_id();
				String data_cate = iary.getData_cate();
				String data_id = iary.getData_id();
				String data_ext = iary.getData_ext();
				String data_item = iary.getData_item();
				String ext_1 = iary.getExt_1();
				String ext_2 = iary.getExt_2();
				String ext_3 = iary.getExt_3();
				String ext_4 = iary.getExt_4();
				String ext_5 = iary.getExt_5();
				String data_desc = iary.getData_desc();

				if (data_seq_id != null) {
					bis_data = bisDataDao.get(data_seq_id);
				} else {
					String hql = " from Bis_data where data_cate=?" + "and data_id= ? and data_ext=?";
					bis_data = (Bis_data) bisDataDao.uniqueResultWithLock(hql, data_cate, data_id, data_ext);
				}
				if (bis_data == null) {
					xplstdatO.setRtn_code(E_BIS_DATA + E_READ_NOT_FOUND + "");
					xplstdatO.setRtn_mesg("[Bis_data] Record Not Found");
					return xplstdatO;
				}
				if (data_ext != null) {
					bis_data.setData_ext(data_ext);
				}
				if (data_item != null) {
					bis_data.setData_item(data_item);
				}
				if (ext_1 != null) {
					bis_data.setExt_1(ext_1);
				}
				if (ext_2 != null) {
					bis_data.setExt_2(ext_2);
				}
				if (ext_3 != null) {
					bis_data.setExt_3(ext_3);
				}
				if (ext_4 != null) {
					bis_data.setExt_4(ext_4);
				}
				if (ext_5 != null) {
					bis_data.setExt_5(ext_5);
				}
				if (data_desc != null) {
					bis_data.setData_desc(data_desc);
				}
				bisDataDao.update(bis_data);
			}
		} catch (Exception ex) {
			logger.error(Util.stackTraceToString(ex));
			xplstdatO.setRtn_code(E_XPLSTDAT_OTHER_ERROR + "");
			xplstdatO.setRtn_mesg(ex.toString());
			return xplstdatO;
		}
		xplstdatO.setRtn_code(RETURN_CODE_OK);
		xplstdatO.setRtn_mesg("success");
		return xplstdatO;
	}

	public XplstdatO deleteFnc() {
		XplstdatO xplstdatO = new XplstdatO();
		xplstdatO.setTrx_id("XPLSTDAT");
		xplstdatO.setType_id("O");
		try {
			List<XplstdatIA> iaryList = inTrx.getIary();
			for (XplstdatIA iary : iaryList) {
				if (iary.getData_seq_id() != null) {
					bisDataDao.deleteByKey(iary.getData_seq_id());
				} else {
					String data_cate = iary.getData_cate();
					String data_id = iary.getData_id();
					String data_ext = iary.getData_ext();
					String hql = " from Bis_data where data_cate=:data_cate "
							+ "and data_id=:data_id and data_ext=:data_ext";
					Query query = bisDataDao.getCrSession().createQuery(hql);

					query.setString("data_cate", data_cate);
					query.setString("data_id", data_id);
					query.setString("data_ext", data_ext);
					Bis_data bis_data = (Bis_data) query.uniqueResult();
					if (bis_data == null) {
						xplstdatO.setRtn_code(E_BIS_DATA + E_READ_NOT_FOUND + "");
						xplstdatO.setRtn_mesg("[Bis_data] Record Not Found");
						return xplstdatO;
					}
					bisDataDao.delete(bis_data);
				}
			}
		} catch (Exception ex) {
			logger.error(Util.stackTraceToString(ex));
			xplstdatO.setRtn_code(E_XPLSTDAT_OTHER_ERROR + "");
			xplstdatO.setRtn_mesg(ex.toString());
			return xplstdatO;
		}
		xplstdatO.setRtn_code(RETURN_CODE_OK);
		xplstdatO.setRtn_mesg("success");
		return xplstdatO;
	}

	public XplstdatO queryByCondition() {

		XplstdatO xplstdatO = new XplstdatO();
		xplstdatO.setTrx_id("XPLSTDAT");
		xplstdatO.setType_id("O");
		try {
			XplstdatIA iary = inTrx.getIary().get(0);
			String dataCate = iary.getData_cate();
			String hql = "FROM Bis_data where data_cate ='" + iary.getData_cate() + "'";
			if (iary.getData_id() != null && !Util.isSpaceCheck(iary.getData_id())) {
				hql = hql + " AND data_id='" + iary.getData_id() + "' ";
			}
			if (iary.getData_ext() != null && !Util.isSpaceCheck(iary.getData_ext())) {
				hql = hql + " AND data_ext='" + iary.getData_ext() + "'";
			}
			if (iary.getData_item() != null && !Util.isSpaceCheck(iary.getData_item())) {
				hql = hql + " AND data_item ='" + iary.getData_item() + "'";
			}
			if (iary.getExt_1() != null && !Util.isSpaceCheck(iary.getExt_1())) {
				hql = hql + " AND ext_1 ='" + iary.getExt_1() + "'";
			}
			if (iary.getExt_2() != null && !Util.isSpaceCheck(iary.getExt_2())) {
				hql = hql + " AND ext_2 ='" + iary.getExt_2() + "'";
			}
			if (iary.getExt_3() != null && !Util.isSpaceCheck(iary.getExt_3())) {
				hql = hql + " AND ext_3 ='" + iary.getExt_3() + "'";
			}
			if (iary.getExt_4() != null && !Util.isSpaceCheck(iary.getExt_4())) {
				hql = hql + " AND ext_4 ='" + iary.getExt_4() + "'";
			}
			if (iary.getExt_5() != null && !Util.isSpaceCheck(iary.getExt_5())) {
				hql = hql + " AND ext_5 ='" + iary.getExt_5() + "'";
			}
			if ("CUSD".equals(dataCate)) {
				if (!"Y".equals(iary.getList_all_cus_flg())) {
					hql = hql + " AND EXT_5 <>'2'";
				}
				hql = hql + " ORDER BY data_item";
			} else {
				hql = hql + " ORDER BY DATA_CATE,DATA_ID,DATA_EXT";
			}
			List<Bis_data> bis_dataList = bisDataDao.find(hql);
			List<XplstdatOA> oaryList = new ArrayList<XplstdatOA>();
			for (Bis_data bis_data : bis_dataList) {
				XplstdatOA oary = new XplstdatOA();
				oary.setData_seq_id(bis_data.getData_seq_id());
				oary.setData_cate(bis_data.getData_cate());
				oary.setData_id(bis_data.getData_id());
				oary.setData_ext(bis_data.getData_ext());
				oary.setData_item(bis_data.getData_item());
				oary.setData_desc(bis_data.getData_desc());
				oary.setExt_1(bis_data.getExt_1());
				oary.setExt_2(bis_data.getExt_2());
				oary.setExt_3(bis_data.getExt_3());
				oary.setExt_4(bis_data.getExt_4());
				oary.setExt_5(bis_data.getExt_5());
				oaryList.add(oary);
			}
			xplstdatO.setTbl_cnt(oaryList.size());
			xplstdatO.setOary(oaryList);
		} catch (Exception ex) {
			logger.error(Util.stackTraceToString(ex));
			xplstdatO.setRtn_code(E_XPLSTDAT_OTHER_ERROR + "");
			xplstdatO.setRtn_mesg(ex.toString());
			return xplstdatO;
		}
		xplstdatO.setRtn_code(RETURN_CODE_OK);
		xplstdatO.setRtn_mesg("success");
		return xplstdatO;

	}

	public XplstdatO queryByConditioncs() {
		XplstdatO xplstdatO = new XplstdatO();
		xplstdatO.setTrx_id("XPLSTDAT");
		xplstdatO.setType_id("O");
		try {
			XplstdatIA iary = inTrx.getIary().get(0);
			String dataCate = iary.getData_cate();
			String hql = "FROM Bis_data where data_cate ='" + iary.getData_cate() + "'";
			if (iary.getData_id() != null && !Util.isSpaceCheck(iary.getData_id())) {
				hql = hql + " AND data_id='" + iary.getData_id() + "' ";
			}
			if (iary.getData_ext() != null && !Util.isSpaceCheck(iary.getData_ext())) {
				hql = hql + " AND data_ext='" + iary.getData_ext() + "'";
			}
			if (iary.getData_item() != null && !Util.isSpaceCheck(iary.getData_item())) {
				hql = hql + " AND data_item ='" + iary.getData_item() + "'";
			}
			if (iary.getExt_1() != null && !Util.isSpaceCheck(iary.getExt_1())) {
				hql = hql + " AND ext_1 ='" + iary.getExt_1() + "'";
			}
			if (iary.getExt_2() != null && !Util.isSpaceCheck(iary.getExt_2())) {
				hql = hql + " AND ext_2 ='" + iary.getExt_2() + "'";
			}
			if (iary.getExt_3() != null && !Util.isSpaceCheck(iary.getExt_3())) {
				hql = hql + " AND ext_3 ='" + iary.getExt_3() + "'";
			}
			if (iary.getExt_4() != null && !Util.isSpaceCheck(iary.getExt_4())) {
				hql = hql + " AND ext_4 ='" + iary.getExt_4() + "'";
			}
			if (iary.getExt_5() != null && !Util.isSpaceCheck(iary.getExt_5())) {
				hql = hql + " AND ext_5 ='" + iary.getExt_5() + "'";
			}
			if ("CUSD".equals(dataCate)) {
				if (!"Y".equals(iary.getList_all_cus_flg())) {
					hql = hql + " AND EXT_5 <>'2'";
				}
				hql = hql + " ORDER BY data_item";
			} else {
				hql = hql + " ORDER BY DATA_CATE,DATA_ID,DATA_EXT";
			}
			List<Bis_data> bis_dataList = bisDataDao.find(hql);
			List<XplstdatOA> oaryList = new ArrayList<XplstdatOA>();
			for (Bis_data bis_data : bis_dataList) {
				XplstdatOA oary = new XplstdatOA();
				oary.setData_seq_id(bis_data.getData_seq_id());
				oary.setData_cate(bis_data.getData_cate());
				oary.setData_id(bis_data.getData_id());
				oary.setData_ext(bis_data.getData_ext());
				oary.setData_item(bis_data.getData_item());
				oary.setData_desc(bis_data.getData_desc());
				oary.setExt_1(bis_data.getExt_1());
				oary.setExt_2(bis_data.getExt_2());
				oary.setExt_3(bis_data.getExt_3());
				oary.setExt_4(bis_data.getExt_4());
				oary.setExt_5(bis_data.getExt_5());
				oaryList.add(oary);
			}
			xplstdatO.setTbl_cnt(oaryList.size());
			xplstdatO.setOary(oaryList);
		} catch (Exception ex) {
			logger.error(Util.stackTraceToString(ex));
			xplstdatO.setRtn_code(E_XPLSTDAT_OTHER_ERROR + "");
			xplstdatO.setRtn_mesg(ex.toString());
			return xplstdatO;
		}
		xplstdatO.setRtn_code(RETURN_CODE_OK);
		xplstdatO.setRtn_mesg("success");
		return xplstdatO;
	}

	public XplstdatO queryformaker() {
		XplstdatO xplstdatO = new XplstdatO();
		xplstdatO.setTrx_id("XPLSTDAT");
		xplstdatO.setType_id("O");
		try {
			XplstdatIA iary = inTrx.getIary().get(0);
			String dataCate = iary.getData_cate();
			String hql = "FROM Bis_data where data_cate ='CUSD' and ext_5 in ('1','2') and data_ext like '%"
					+ iary.getData_ext() + "%'";
			List<Bis_data> bis_dataList = bisDataDao.find(hql);
			List<XplstdatOA> oaryList = new ArrayList<XplstdatOA>();
			for (Bis_data bis_data : bis_dataList) {
				XplstdatOA oary = new XplstdatOA();
				oary.setData_seq_id(bis_data.getData_seq_id());
				oary.setData_cate(bis_data.getData_cate());
				oary.setData_id(bis_data.getData_id());
				oary.setData_ext(bis_data.getData_ext());
				oary.setData_item(bis_data.getData_item());
				oary.setData_desc(bis_data.getData_desc());
				oary.setExt_1(bis_data.getExt_1());
				oary.setExt_2(bis_data.getExt_2());
				oary.setExt_3(bis_data.getExt_3());
				oary.setExt_4(bis_data.getExt_4());
				oary.setExt_5(bis_data.getExt_5());
				oaryList.add(oary);
			}
			xplstdatO.setTbl_cnt(oaryList.size());
			xplstdatO.setOary(oaryList);
		} catch (Exception ex) {
			logger.error(Util.stackTraceToString(ex));
			xplstdatO.setRtn_code(E_XPLSTDAT_OTHER_ERROR + "");
			xplstdatO.setRtn_mesg(ex.toString());
			return xplstdatO;
		}
		xplstdatO.setRtn_code(RETURN_CODE_OK);
		xplstdatO.setRtn_mesg("success");
		return xplstdatO;
	}

	public XplstdatO addDataCateProc() {
		XplstdatO xplstdatO = new XplstdatO();
		xplstdatO.setTrx_id("XPLSTDAT");
		xplstdatO.setType_id("O");
		try {
			XplstdatIA iary = inTrx.getIary().get(0);
			String data_cate = iary.getData_cate();
			String data_desc = iary.getData_desc();
			String hql = "FROM Bis_data where data_cate='CATE' and data_id=:data_id";
			Query query = bisDataDao.getCrSession().createQuery(hql);
			query.setString("data_id", data_cate);
			if (query.list().size() > 0) {
				xplstdatO.setRtn_code(E_BIS_DATA + E_ADD_EXIST + "");
				xplstdatO.setRtn_mesg("Data Cate Already Exist,Please check");
				return xplstdatO;
			}
			Bis_data bis_data = new Bis_data();
			bis_data.setData_seq_id(GUIDGenerator.javaGUID());
			bis_data.setData_cate("CATE");
			bis_data.setData_id(data_cate);
			bis_data.setData_desc(data_desc);
			bisDataDao.save(bis_data);
			if (!Util.isSpaceCheck(iary.getDsc_data_id())) {
				Bis_data bis_data_1 = new Bis_data();
				bis_data_1.setData_seq_id(GUIDGenerator.javaGUID());
				bis_data_1.setData_cate("CODE");
				bis_data_1.setData_id(data_cate);
				bis_data_1.setData_ext("DATA_ID");
				bis_data_1.setData_desc(iary.getDsc_data_id());
				bisDataDao.save(bis_data_1);
			}
			if (!Util.isSpaceCheck(iary.getDsc_data_ext())) {
				Bis_data bis_data_2 = new Bis_data();
				bis_data_2.setData_seq_id(GUIDGenerator.javaGUID());
				bis_data_2.setData_cate("CODE");
				bis_data_2.setData_id(data_cate);
				bis_data_2.setData_ext("DATA_EXT");
				bis_data_2.setData_desc(iary.getDsc_data_ext());
				bisDataDao.save(bis_data_2);
			}
			if (!Util.isSpaceCheck(iary.getDsc_data_item())) {
				Bis_data bis_data_3 = new Bis_data();
				bis_data_3.setData_seq_id(GUIDGenerator.javaGUID());
				bis_data_3.setData_cate("CODE");
				bis_data_3.setData_id(data_cate);
				bis_data_3.setData_ext("DATA_ITEM");
				bis_data_3.setData_desc(iary.getDsc_data_item());
				bisDataDao.save(bis_data_3);
			}
			if (!Util.isSpaceCheck(iary.getDsc_ext_1())) {
				Bis_data bis_data_4 = new Bis_data();
				bis_data_4.setData_seq_id(GUIDGenerator.javaGUID());
				bis_data_4.setData_cate("CODE");
				bis_data_4.setData_id(data_cate);
				bis_data_4.setData_ext("EXT_1");
				bis_data_4.setData_desc(iary.getDsc_ext_1());
				bisDataDao.save(bis_data_4);
			}
			if (!Util.isSpaceCheck(iary.getDsc_ext_2())) {
				Bis_data bis_data_5 = new Bis_data();
				bis_data_5.setData_seq_id(GUIDGenerator.javaGUID());
				bis_data_5.setData_cate("CODE");
				bis_data_5.setData_id(data_cate);
				bis_data_5.setData_ext("EXT_2");
				bis_data_5.setData_desc(iary.getDsc_ext_2());
				bisDataDao.save(bis_data_5);
			}
			if (!Util.isSpaceCheck(iary.getDsc_ext_3())) {
				Bis_data bis_data_6 = new Bis_data();
				bis_data_6.setData_seq_id(GUIDGenerator.javaGUID());
				bis_data_6.setData_cate("CODE");
				bis_data_6.setData_id(data_cate);
				bis_data_6.setData_ext("EXT_3");
				bis_data_6.setData_desc(iary.getDsc_ext_3());
				bisDataDao.save(bis_data_6);
			}
			if (!Util.isSpaceCheck(iary.getDsc_ext_4())) {
				Bis_data bis_data_7 = new Bis_data();
				bis_data_7.setData_seq_id(GUIDGenerator.javaGUID());
				bis_data_7.setData_cate("CODE");
				bis_data_7.setData_id(data_cate);
				bis_data_7.setData_ext("EXT_4");
				bis_data_7.setData_desc(iary.getDsc_ext_4());
				bisDataDao.save(bis_data_7);
			}
			if (!Util.isSpaceCheck(iary.getDsc_ext_5())) {
				Bis_data bis_data_8 = new Bis_data();
				bis_data_8.setData_seq_id(GUIDGenerator.javaGUID());
				bis_data_8.setData_cate("CODE");
				bis_data_8.setData_id(data_cate);
				bis_data_8.setData_ext("EXT_5");
				bis_data_8.setData_desc(iary.getDsc_ext_5());
				bisDataDao.save(bis_data_8);
			}
			if (!Util.isSpaceCheck(iary.getDsc_data_dsc())) {
				Bis_data bis_data_9 = new Bis_data();
				bis_data_9.setData_seq_id(GUIDGenerator.javaGUID());
				bis_data_9.setData_cate("CODE");
				bis_data_9.setData_id(data_cate);
				bis_data_9.setData_ext("DATA_DESC");
				bis_data_9.setData_desc(iary.getDsc_data_dsc());
				bisDataDao.save(bis_data_9);
			}
		} catch (Exception ex) {
			logger.error(Util.stackTraceToString(ex));
			xplstdatO.setRtn_code(E_XPLSTDAT_OTHER_ERROR + "");
			xplstdatO.setRtn_mesg(ex.toString());
			return xplstdatO;
		}
		xplstdatO.setRtn_code(RETURN_CODE_OK);
		xplstdatO.setRtn_mesg("success");
		return xplstdatO;
	}

	public XplstdatO updateDataCateProc() {
		XplstdatO xplstdatO = new XplstdatO();
		xplstdatO.setTrx_id("XPLSTDAT");
		xplstdatO.setType_id("O");
		try {
			XplstdatIA iary = inTrx.getIary().get(0);
			String hql = "FROM Bis_data where data_cate='CATE' and " + "data_id=:data_id";
			Query query = bisDataDao.getCrSession().createQuery(hql);
			query.setString("data_id", iary.getData_cate());
			Bis_data bis_data = (Bis_data) query.uniqueResult();
			if (bis_data == null) {
				xplstdatO.setRtn_code(E_BIS_DATA + E_READ_NOT_FOUND + "");
				xplstdatO.setRtn_mesg("not found record in Bis_data");
				return xplstdatO;
			}
		} catch (Exception ex) {
			logger.error(Util.stackTraceToString(ex));
			xplstdatO.setRtn_code(E_XPLSTDAT_OTHER_ERROR + "");
			xplstdatO.setRtn_mesg(ex.toString());
			return xplstdatO;
		}
		xplstdatO.setRtn_code(RETURN_CODE_OK);
		xplstdatO.setRtn_mesg("success");
		return xplstdatO;
	}

	public XplstdatO deleteDataCateProc() {
		XplstdatO xplstdatO = new XplstdatO();
		xplstdatO.setTrx_id("XPLSTDAT");
		xplstdatO.setType_id("O");
		try {
			XplstdatIA iary = inTrx.getIary().get(0);
			String data_cate = iary.getData_cate();
			String hql = "FROM Bis_data where data_cate='CATE' and data_id=:data_id ";
			Session session = bisDataDao.getCrSession();
			Query query = session.createQuery(hql);
			query.setString("data_id", data_cate);
			Bis_data bis_data = (Bis_data) query.uniqueResult();
			if (bis_data == null) {
				xplstdatO.setRtn_code(E_BIS_DATA + E_READ_NOT_FOUND + "");
				xplstdatO.setRtn_mesg("[Bis_data] Record Not Found");
				return xplstdatO;
			}
			bisDataDao.delete(bis_data);

			String hql2 = "FROM Bis_data where data_cate=:data_cate ";
			Session session2 = bisDataDao.getCrSession();
			Query query2 = session2.createQuery(hql2);

			query2.setString("data_cate", data_cate);
			List<Bis_data> bis_dataList = query2.list();
			for (Bis_data bis_data2 : bis_dataList) {
				bisDataDao.delete(bis_data2);
			}

			String hql3 = "FROM Bis_data where data_cate='CODE' and data_id=:data_id  ";
			Session session3 = bisDataDao.getCrSession();
			Query query3 = session3.createQuery(hql3);

			query3.setString("data_id", data_cate);
			List<Bis_data> bis_dataList1 = query3.list();
			for (Bis_data bis_data3 : bis_dataList1) {
				bisDataDao.delete(bis_data3);
			}

		} catch (Exception ex) {
			xplstdatO.setRtn_code(E_XPLSTDAT_OTHER_ERROR + "");
			xplstdatO.setRtn_mesg(ex.getMessage());
			return xplstdatO;
		}
		xplstdatO.setRtn_code(RETURN_CODE_OK);
		xplstdatO.setRtn_mesg("success");
		return xplstdatO;
	}

	public XplstdatO querybycate() {
		XplstdatO xplstdatO = new XplstdatO();
		xplstdatO.setTrx_id("XPLSTDAT");
		xplstdatO.setType_id("O");
		try {
			XplstdatIA iary = inTrx.getIary().get(0);
			String dataCate = iary.getData_cate();
			String sql = "SELECT A.* FROM BIS_DATA A JOIN BIS_MTRL B ON A.EXT_1=B.MTRL_PROD_ID WHERE A.DATA_CATE ='CSMT' "
					+ "AND B.MTRL_CATE='SHMT' AND A.DATA_ITEM ='" + iary.getData_item() + "'";
			List<Bis_data> bis_dataList = bisDataDao.findBySQL(sql, Bis_data.class);
			List<XplstdatOA> oaryList = new ArrayList<XplstdatOA>();
			for (Bis_data bis_data : bis_dataList) {
				XplstdatOA oary = new XplstdatOA();
				oary.setData_seq_id(bis_data.getData_seq_id());
				oary.setData_cate(bis_data.getData_cate());
				oary.setData_id(bis_data.getData_id());
				oary.setData_ext(bis_data.getData_ext());
				oary.setData_item(bis_data.getData_item());
				oary.setData_desc(bis_data.getData_desc());
				oary.setExt_1(bis_data.getExt_1());
				oary.setExt_2(bis_data.getExt_2());
				oary.setExt_3(bis_data.getExt_3());
				oary.setExt_4(bis_data.getExt_4());
				oary.setExt_5(bis_data.getExt_5());
				oaryList.add(oary);
			}
			xplstdatO.setTbl_cnt(oaryList.size());
			xplstdatO.setOary(oaryList);
		} catch (Exception ex) {
			logger.error(Util.stackTraceToString(ex));
			xplstdatO.setRtn_code(E_XPLSTDAT_OTHER_ERROR + "");
			xplstdatO.setRtn_mesg(ex.toString());
			return xplstdatO;
		}
		xplstdatO.setRtn_code(RETURN_CODE_OK);
		xplstdatO.setRtn_mesg("success");
		return xplstdatO;
	}

	public XplstdatO queryByExt() {
		XplstdatO xplstdatO = new XplstdatO();
		xplstdatO.setTrx_id("XPLSTDAT");
		xplstdatO.setType_id("O");
		try {
			XplstdatIA iary = inTrx.getIary().get(0);
			String hql = "FROM Bis_data where data_cate ='" + iary.getData_cate() + "'";
			if (iary.getData_ext() != null) {
				hql = hql + " AND data_ext in(" + iary.getData_ext() + ")";
			}
			List<Bis_data> bis_dataList = bisDataDao.find(hql);
			int tbl_cnt = bis_dataList.size();
			List<XplstdatOA> oaryList = new ArrayList<XplstdatOA>();
			for (Bis_data bis_data : bis_dataList) {
				XplstdatOA oary = new XplstdatOA();
				oary.setData_cate(bis_data.getData_cate());
				oary.setData_id(bis_data.getData_id());
				oary.setData_ext(bis_data.getData_ext());
				oary.setData_item(bis_data.getData_item());
				oary.setData_desc(bis_data.getData_desc());
				oary.setExt_1(bis_data.getExt_1());
				oary.setExt_2(bis_data.getExt_2());
				oary.setExt_3(bis_data.getExt_3());
				oary.setExt_4(bis_data.getExt_4());
				oary.setExt_5(bis_data.getExt_5());
				oaryList.add(oary);
			}
			xplstdatO.setTbl_cnt(oaryList.size());
			xplstdatO.setOary(oaryList);
		} catch (Exception ex) {
			logger.error(Util.stackTraceToString(ex));
			xplstdatO.setRtn_code(E_XPLSTDAT_OTHER_ERROR + "");
			xplstdatO.setRtn_mesg(ex.toString());
			return xplstdatO;
		}
		xplstdatO.setRtn_code(RETURN_CODE_OK);
		xplstdatO.setRtn_mesg("success");
		return xplstdatO;
	}

	public XplstdatO queryByExt5() {
		XplstdatO xplstdatO = new XplstdatO();
		xplstdatO.setTrx_id("XPLSTDAT");
		xplstdatO.setType_id("O");
		try {
			XplstdatIA iary = inTrx.getIary().get(0);
			String hql = "FROM Bis_data where data_cate ='" + iary.getData_cate() + "'";
			if (iary.getExt_5() != null) {
				hql = hql + " AND ext_5 in(" + iary.getExt_5() + ")";
			}
			List<Bis_data> bis_dataList = bisDataDao.find(hql);
			int tbl_cnt = bis_dataList.size();
			List<XplstdatOA> oaryList = new ArrayList<XplstdatOA>();
			for (Bis_data bis_data : bis_dataList) {
				XplstdatOA oary = new XplstdatOA();
				oary.setData_cate(bis_data.getData_cate());
				oary.setData_id(bis_data.getData_id());
				oary.setData_ext(bis_data.getData_ext());
				oary.setData_item(bis_data.getData_item());
				oary.setData_desc(bis_data.getData_desc());
				oary.setExt_1(bis_data.getExt_1());
				oary.setExt_2(bis_data.getExt_2());
				oary.setExt_3(bis_data.getExt_3());
				oary.setExt_4(bis_data.getExt_4());
				oary.setExt_5(bis_data.getExt_5());
				oaryList.add(oary);
			}
			xplstdatO.setTbl_cnt(oaryList.size());
			xplstdatO.setOary(oaryList);
		} catch (Exception ex) {
			logger.error(Util.stackTraceToString(ex));
			xplstdatO.setRtn_code(E_XPLSTDAT_OTHER_ERROR + "");
			xplstdatO.setRtn_mesg(ex.toString());
			return xplstdatO;
		}
		xplstdatO.setRtn_code(RETURN_CODE_OK);
		xplstdatO.setRtn_mesg("success");
		return xplstdatO;
	}

	public XplstdatO errActionFnc() {
		XplstdatO xplstdatO = new XplstdatO();
		xplstdatO.setTrx_id("XPLSTDAT");
		xplstdatO.setType_id("O");
		xplstdatO.setRtn_code("0000009");
		xplstdatO.setRtn_mesg("Invalid Action_flg");
		return xplstdatO;
	}

	private XplstdatO deleteByDataCateExt1() {
		XplstdatIA iary = inTrx.getIary().get(0);
		String data_cate, data_ext, ext_1;
		data_cate = iary.getData_cate();
		data_ext = iary.getData_ext();
		ext_1 = iary.getExt_1();
		String hql = "FROM Bis_data WHERE DATA_CATE=? AND DATA_EXT=? AND EXT_1=?";
		Bis_data bis_data = bisDataDao.uniqueResult(hql, data_cate, data_ext, ext_1);
		if (bis_data == null) {
		} else {
			bisDataDao.delete(bis_data);
		}
		outTrx.setRtn_code(RETURN_CODE_OK);
		outTrx.setRtn_mesg(RETURN_MESG_OK);
		return outTrx;
	}

	private XplstdatO addByDataCateExt1() {
		int i = 0;
		String data_cate, data_ext, ext_1, ext_2, data_desc;
		for (i = 0; i < inTrx.getTbl_cnt(); i++) {
			XplstdatIA iary = inTrx.getIary().get(i);
			data_cate = iary.getData_cate();
			data_ext = iary.getData_ext();
			ext_1 = iary.getExt_1();
			ext_2 = iary.getExt_2();
			data_desc = iary.getData_desc();
			String sql = "FROM Bis_data WHERE DATA_CATE=? AND DATA_EXT=? AND EXT_1=?";
			List<Bis_data> bis_data_list = bisDataDao.listWithLock(sql, data_cate, data_ext, ext_1);
			if (bis_data_list.size() > 1) {
				outTrx.setRtn_code(RETURN_CODE_SET_ERROR);
				outTrx.setRtn_mesg("此记录不止一条，请检查！");
				return outTrx;
			}
			/**
			 * 是否存储厂别在UROP(人员与站点的关系)
			 */
			if (bis_data_list.size() == 0) {
				Bis_data bis_data = new Bis_data();
				bis_data.setData_seq_id(GUIDGenerator.javaGUID());
				bis_data.setUnq_seq_id(GUIDGenerator.javaGUID());
				bis_data.setData_cate(data_cate);
				bis_data.setData_ext(data_ext);
				bis_data.setData_desc(data_desc);
				bis_data.setExt_1(ext_1);
				bis_data.setExt_2(ext_2);
				bisDataDao.save(bis_data);
			} else if (bis_data_list.size() == 1) {
				Bis_data bis_data = bis_data_list.get(0);
				bis_data.setExt_2(ext_2);
				bis_data.setData_desc(data_desc);
				bisDataDao.update(bis_data);
			}
		}
		outTrx.setRtn_code(RETURN_CODE_OK);
		outTrx.setRtn_mesg(RETURN_MESG_OK);
		return outTrx;
	}

	private XplstdatO queryDataByCateUser() {
		String data_cate = "", data_ext = "", user_id = "";
		int i = 0;
		List<XplstdatOA> oaryList = new ArrayList<XplstdatOA>();
		List<Bis_data> bisDataList = new ArrayList<Bis_data>();
		XplstdatIA iary = inTrx.getIary().get(0);
		data_cate = iary.getData_cate();
		data_ext = iary.getData_ext();
		user_id = iary.getUser_id();
		String admin_sql = "FROM Bis_data where data_cate='UFUG'AND DATA_EXT='" + user_id
				+ "' AND EXT_1='ADMIN'AND EXT_3='Y'";
		List<Bis_data> UFUG_list = bisDataDao.find(admin_sql);
		if (UFUG_list.size() == 1) {
			String hql = "FROM Bis_data where data_cate ='" + data_cate + "'";
			if (iary.getData_ext() != null) {
				hql = hql + " AND data_ext in(" + iary.getData_ext() + ") ORDER BY CONVERT(DATA_ITEM,UNSIGNED)";
			}
			bisDataList = bisDataDao.find(hql);
		} else {
			String sql = "SELECT * FROM BIS_DATA A  JOIN BIS_DATA B ON A.DATA_ID = B.EXT_1 WHERE  " + "A.DATA_CATE ='"
					+ data_cate + "' AND A.DATA_EXT IN(" + data_ext + ")" + "AND B.DATA_CATE IN('URDT')AND B.DATA_EXT='"
					+ user_id + "' ORDER BY CONVERT(B.DATA_ITEM,UNSIGNED)";
			bisDataList = bisDataDao.findBySQL(sql, Bis_data.class);
		}
		for (Bis_data bis_data : bisDataList) {
			XplstdatOA oary = new XplstdatOA();
			oary.setData_cate(bis_data.getData_cate());
			oary.setData_desc(bis_data.getData_desc());
			oary.setData_ext(bis_data.getData_ext());
			oary.setData_id(bis_data.getData_id());
			oary.setData_item(bis_data.getData_item());
			oary.setExt_1(bis_data.getExt_1());
			oary.setExt_2(bis_data.getExt_2());
			oary.setExt_3(bis_data.getExt_3());
			oary.setExt_4(bis_data.getExt_4());
			oary.setExt_5(bis_data.getExt_5());
			oaryList.add(oary);
		}
		outTrx.setRtn_code(RETURN_CODE_OK);
		outTrx.setRtn_mesg(RETURN_MESG_OK);
		outTrx.setOary(oaryList);
		outTrx.setTbl_cnt(oaryList.size());
		return outTrx;
	}

	private XplstdatO queryDataByFabUser() {
		String data_cate = "", data_ext = "", user_id = "";
		List<XplstdatOA> oaryList = new ArrayList<XplstdatOA>();
		List<Bis_data> bisDataList = new ArrayList<Bis_data>();
		XplstdatIA iary = inTrx.getIary().get(0);
		data_cate = iary.getData_cate();
		data_ext = iary.getData_ext();
		user_id = iary.getUser_id();
		String admin_sql = "FROM Bis_data where data_cate='UFUG'AND DATA_EXT='" + user_id
				+ "' AND EXT_1='ADMIN'AND EXT_3='Y'";
		List<Bis_data> UFUG_list = bisDataDao.find(admin_sql);
		if (UFUG_list.size() == 1) {
			String hql = "FROM Bis_data where data_cate ='" + data_cate + "'";
			if (iary.getData_ext() != null) {
				hql = hql + " AND data_ext in(" + iary.getData_ext() + ") ORDER BY CONVERT(DATA_ITEM,UNSIGNED)";
			}
			bisDataList = bisDataDao.find(hql);
		} else {
			String sql = " SELECT * FROM BIS_DATA A  JOIN BIS_DATA B ON A.DATA_ID = B.EXT_1 WHERE ";
			sql += " A.DATA_CATE = '" + data_cate + "' AND A.DATA_EXT IN ( " + data_ext + " ) "
					+ " AND B.DATA_CATE IN('URDT')AND B.DATA_EXT='" + user_id
					+ "' ORDER BY CONVERT(B.DATA_ITEM,UNSIGNED)";
			bisDataList = bisDataDao.findBySQL(sql, Bis_data.class);
		}
		for (Bis_data bis_data : bisDataList) {
			XplstdatOA oary = new XplstdatOA();
			oary.setData_cate(bis_data.getData_cate());
			oary.setData_desc(bis_data.getData_desc());
			oary.setData_ext(bis_data.getData_ext());
			oary.setData_id(bis_data.getData_id());
			oary.setData_item(bis_data.getData_item());
			oary.setExt_1(bis_data.getExt_1());
			oary.setExt_2(bis_data.getExt_2());
			oary.setExt_3(bis_data.getExt_3());
			oary.setExt_4(bis_data.getExt_4());
			oary.setExt_5(bis_data.getExt_5());
			oaryList.add(oary);
		}
		outTrx.setRtn_code(RETURN_CODE_OK);
		outTrx.setRtn_mesg(RETURN_MESG_OK);
		outTrx.setOary(oaryList);
		outTrx.setTbl_cnt(oaryList.size());
		return outTrx;
	}

	private XplstdatO listDataBySort() {
		String data_cate, data_ext;
		List<XplstdatOA> oaryList = new ArrayList<XplstdatOA>();
		List<Bis_data> bisDataList = new ArrayList<Bis_data>();
		XplstdatIA iary = inTrx.getIary().get(0);
		data_cate = iary.getData_cate();
		data_ext = iary.getData_ext();
		String sql = "FROM Bis_data where data_cate='" + data_cate + "'AND data_ext='" + data_ext
				+ "'ORDER BY CONVERT(EXT_2,UNSIGNED)";
		bisDataList = bisDataDao.find(sql);
		for (Bis_data bis_data : bisDataList) {
			XplstdatOA oary = new XplstdatOA();
			oary.setData_cate(bis_data.getData_cate());
			oary.setData_desc(bis_data.getData_desc());
			oary.setData_ext(bis_data.getData_ext());
			oary.setData_id(bis_data.getData_id());
			oary.setData_item(bis_data.getData_item());
			oary.setExt_1(bis_data.getExt_1());
			oary.setExt_2(bis_data.getExt_2());
			oary.setExt_3(bis_data.getExt_3());
			oary.setExt_4(bis_data.getExt_4());
			oary.setExt_5(bis_data.getExt_5());
			oaryList.add(oary);
		}
		outTrx.setRtn_code(RETURN_CODE_OK);
		outTrx.setRtn_mesg(RETURN_MESG_OK);
		outTrx.setOary(oaryList);
		outTrx.setTbl_cnt(oaryList.size());
		return outTrx;
	}

	private Bis_data getDataByKeyProc(String data_cate, String data_id, String data_ext) {
		String hql = "FROM Bis_data where data_cate =:data_cate AND " + "data_id=:data_id AND " + "data_ext=:data_ext";

		Query query = bisDataDao.getCrSession().createQuery(hql);
		query.setString("data_cate", data_cate);
		query.setString("data_id", data_id);
		query.setString("data_ext", data_ext);
		Bis_data bis_data = (Bis_data) query.uniqueResult();
		return bis_data;
	}
}
